package com.icim.service.impl;

import com.icim.exception.LoadMainException;
import com.icim.rabbitmq.util.RabbitmqManager;
import com.icim.service.ILoadMainService;
import com.icim.table.Column;
import com.icim.table.Table;
import com.icim.table.TableConfig;
import com.icim.util.DBUtil;
import com.icim.util.Dom4JUtil;
import com.icim.util.TableConfigUtil;
import com.mysql.jdbc.Util;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import static com.icim.define.CommDefine.*;

public class LoadMainService implements ILoadMainService {
    private Logger logger = Logger.getLogger(LoadMainService.class);

    private String hisQueueName;
    private String hisErrorQueueName;

    @Autowired
    private RabbitmqManager rabbitmqManager;

    @Resource
    private TableConfigUtil tableConfigUtil;

    @Resource
    private DBUtil dBUtil;

    public static void main(String[] args) {
        ApplicationContext ctx = new ClassPathXmlApplicationContext("classpath:applicationContext.xml",
                "classpath:applicationContext-service.xml", "classpath:applicationContext-rabbitmq.xml");
        LoadMainService loadMainService = ctx.getBean("loadErrorMainService",LoadMainService.class);
//        String message = "<transaction><trans_cnt>1</trans_cnt><table><box_std_qty>25</box_std_qty><box_weight>0.0</box_weight><evt_cate>MTIN</evt_cate><evt_timestamp>2015-04-26 12:18:58.0</evt_timestamp><evt_usr>01922</evt_usr><cus_id>088</cus_id><mtrl_box_id>E44C498C0001E2544C498C0001N25</mtrl_box_id><mtrl_gd/><mtrl_lot_id/><mtrl_pallet_id/><mtrl_prod_id_fk>C1500B300</mtrl_prod_id_fk><ope_evt_name>XpwhsopeService</ope_evt_name><ope_evt_no>8OSDCFH11LK14UI2MX6AJI79I</ope_evt_no><ope_evt_node>192.168.2.200</ope_evt_node><ope_evt_usr>wg-mes-ser</ope_evt_usr><ope_tbl_name>RET_PRD_IN</ope_tbl_name><operation>INSERT</operation><ppbox_stat>WAIT</ppbox_stat><prd_act_qty>25</prd_act_qty><prd_proc_qty>0</prd_proc_qty><prd_tl_qty>25</prd_tl_qty><pv_unq_seq_id/><rcv_timestamp>2015-04-26 12:18:58.0</rcv_timestamp><rcv_usr>01922</rcv_usr><relate_usr/><sequence/><so_id_fk/><sta_time_d>42120.51317387731</sta_time_d><stb_shop>C202</stb_shop><unq_seq_id>E0OIJ1NBBG6U2WFR5MLTZXRUN</unq_seq_id><vdr_box_id>E44C498C0001E2544C498C0001N25</vdr_box_id><wo_id_fk>LJD707259</wo_id_fk></table></transaction>";
        String message ="<transaction><trans_cnt>1</trans_cnt><table><data_cate>AREA</data_cate><data_desc>1线</data_desc><data_ext>1</data_ext><data_id/><data_item>M1</data_item><data_seq_id>68A8SEO883LCBZ4WSD3HSGMX8</data_seq_id><ext_1/><ext_2/><ext_3/><ext_4/><ext_5/><ope_evt_name>FbpbisdataService</ope_evt_name><ope_evt_no>BG25K6C8GSP2P9THBYXHZNI0O</ope_evt_no><ope_evt_node>172.16.10.244</ope_evt_node><ope_evt_usr>yangsen-PC</ope_evt_usr><ope_tbl_name>BIS_DATA</ope_tbl_name><operation>UPDATE</operation><pv_unq_seq_id>3DOX5S4FCAQOP85TQLYSTL2HB</pv_unq_seq_id><sequence/><sta_time_d>43046.6747006713</sta_time_d><unq_seq_id>6THXBWEK6T8OXFAF3CLR1YDFC</unq_seq_id></table></transaction>";
        try {
            loadMainService.SubMainProc(message);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @SuppressWarnings("unchecked")
    public long SubMainProc(String message) throws LoadMainException {
        long lRc = INITIAL_RETURN_CODE;
        String errorMessage = null;
        List<Element> tableElemList = null;
        try {
            Document doc = DocumentHelper.parseText(message);
            Element root = doc.getRootElement();
            tableElemList = root.elements(XML_NODE_TABLE);
            for (Element tableElem : tableElemList) {
                lRc = SubMainProc2(tableElem);
                if (lRc == _NORMAL) {
                    logger.info("I " + "Transaction" + " process successful ..........");
                } else {
                    logger.error("E " + "Transaction" + "process failed " + lRc);
                }
            }

        } catch (DocumentException e) {
            errorMessage = "解析XML时报错" + Util.stackTraceToString(e);
            logger.error(errorMessage);
            throw new LoadMainException(XML_NODE_NOT_INTEGRITY, errorMessage, e);
        }
        /******* 处理每一个 Transaction **********************/

        return lRc;
    }

    /**
     *
     * @param tableElem :
     * @return
     * @throws LoadMainException
     */
    public long SubMainProc2(Element tableElem) throws LoadMainException {
        long lRc = INITIAL_RETURN_CODE;
        String opeTableName = Dom4JUtil.getXmlValue(tableElem, XML_NODE_OPE_TBL_NAME);

        TableConfig tableConfig = tableConfigUtil.getConfig(opeTableName);

        try {
            dBUtil.openConnection();

            checkXmlFormat(tableElem);

            lRc = buildSqlByXml(tableElem, tableConfig);

            dBUtil.commit();
        } catch (LoadMainException e) {
            dBUtil.rollBack();
            e.printStackTrace();
            throw e;
        }catch (Exception ex){
            dBUtil.rollBack();
            ex.printStackTrace();
            throw new LoadMainException(999,"errors",ex);
        }finally {
            dBUtil.closeConnection();
        }

        return lRc;
    }

    /**
     * 检查XML结点是否完整
     *
     * @param tableElem
     * @return
     * @throws LoadMainException
     */
    private void checkXmlFormat(Element tableElem) throws LoadMainException {
        String opeTblName = Dom4JUtil.getXmlValue(tableElem, XML_NODE_OPE_TBL_NAME);
        String operation = Dom4JUtil.getXmlValue(tableElem, XML_NODE_OPERATION);
        String unqSeqId = Dom4JUtil.getXmlValue(tableElem, XML_NODE_UNQ_SEQ_ID);
        String strStaTimeD = Dom4JUtil.getXmlValue(tableElem, XML_NODE_START_TIME_D);
        Double startTimeD = StringUtils.isEmpty(strStaTimeD) ? null : Double.valueOf(strStaTimeD);
        if (StringUtils.isEmpty(opeTblName)) {
            throw new LoadMainException(XML_NODE_NOT_INTEGRITY, "Xml格式不正确,ope_tbl_name不能为空");
        }
        if (StringUtils.isEmpty(operation)) {
            throw new LoadMainException(XML_NODE_NOT_INTEGRITY, "Xml格式不正确,operation 不能为空");
        }
        if (StringUtils.isEmpty(unqSeqId)) {
            throw new LoadMainException(XML_NODE_NOT_INTEGRITY, "Xml格式不正确,unq_seq_id 不能为空");
        }
        if (startTimeD == null) {
            throw new LoadMainException(XML_NODE_NOT_INTEGRITY, "Xml格式不正确,start_time_d不能为空");
        }

    }

    /**
     * 通过读取XML中的内容 拼凑SQL语句 ,pptSql代表与WIP同步的备份表SQL,hisSql代表历史履历表SQL。 分以下三种模式 :
     * (1).transactionMode= TRANSATION_MODE_PPT时,对PPT表执行查找更新写入操作
     * (2).transactionMode=TRANSATION_MODE_HIS时,只对HIS表进行insert操作
     * (3).transactionMode=TRANSATION_MODE_BOTH_PPT_HIS时,执行以上两步操作
     * (4).transactionMode=TRANSATION_MODE_NONE时，结束退出
     *
     * ps:在Perferencs中 Formater建一个自己的模板。将Enable comment formatting前面的勾去掉。
     *
     * @param tableElem
     * @return
     * @throws LoadMainException
     */
    private long buildSqlByXml(Element tableElem, TableConfig configUtil) throws LoadMainException {
        long lRc = INITIAL_RETURN_CODE;
        int transactionMode = configUtil.getTransactionMode();
        String pptTableName = configUtil.getPptTableName();
        String hisTableName = configUtil.getHisTableName();
        StringBuffer logBuf = new StringBuffer();
        logBuf.append(Dom4JUtil.getXmlValue(tableElem, XML_NODE_UNQ_SEQ_ID)).append(":");
        if (!StringUtils.isEmpty(pptTableName)) {
            logBuf.append(pptTableName);
        }
        if (!StringUtils.isEmpty(hisTableName)) {
            logBuf.append(",").append(hisTableName);
        }
        long costTime;
        switch (transactionMode) {

            case TRANSATION_MODE_PPT:
                logger.info("Execute PPT SQL");
                costTime = System.currentTimeMillis();
                lRc = preparePptSql(tableElem, pptTableName);
                logger.info("Execute PPT SQL OK, cost time " + (System.currentTimeMillis() - costTime) + "ms");
                break;

            case TRANSATION_MODE_HIS:
                logger.info("Execute HIS SQL");
                costTime = System.currentTimeMillis();
                lRc = prepareHisSql(tableElem, hisTableName);
                logger.info("Execute HIS SQL OK, cost time " + (System.currentTimeMillis() - costTime) + "ms");
                break;

            case TRANSATION_MODE_BOTH_PPT_HIS:
                logger.info("Execute PPT and HIS SQL");
                costTime = System.currentTimeMillis();
                lRc = preparePptSql(tableElem, pptTableName);
                lRc = prepareHisSql(tableElem, hisTableName);
                logger.info("Execute PPT and HIS SQL OK, cost time " + (System.currentTimeMillis() - costTime) + "ms");
                break;

            default:
                lRc = TRANSATION_MODE_NONE;
                throw new LoadMainException(lRc, "配置文件中没有表[" + configUtil.getOpeTableName() + "]的配置");
        }

        logger.info(logBuf);
        return lRc;
    }

    private void preparePptInsertSql(Element tableElement, Table table) throws LoadMainException {
        StringBuilder sql = new StringBuilder();
        StringBuilder sb1 = new StringBuilder();
        StringBuilder sb2 = new StringBuilder();
        StringBuilder valueBulder =new StringBuilder();
        List<Column> columns = table.getColumnList();
        sql.append("insert into ");
//        sql.append(table.getSchema());
//        sql.append(".");
        sql.append(table.getTableName());
        for (int i = 0; i < columns.size(); i++) {
            Column column = columns.get(i);
            sb1.append(column.getColumnName()).append(",");
            sb2.append("?,");
            Element element = tableElement.element(column.getColumnName().toLowerCase());
            if(element == null){
                valueBulder.append("null");
            }else{
                valueBulder.append(element.getTextTrim());
            }
            valueBulder.append(",");
        }
        sb1.deleteCharAt(sb1.length() - 1);
        sb2.deleteCharAt(sb2.length() - 1);
        sql.append(" (");
        sql.append(sb1);
        sql.append(") values (");
        sql.append(sb2);
        sql.append(")");
        logger.info(sql.toString());
        logger.info(valueBulder.toString());
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dBUtil.getCurrentConnection();
            preparedStatement = connection.prepareStatement(sql.toString());
            final ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();

            for (int i = 0; i < columns.size(); i++) {
//                int parameterIndex = i + 1;
//                int parameterType = parameterMetaData.getParameterType(parameterIndex);

                Column column = columns.get(i);
//                Element element = tableElement.element(column.getColumnName());
//                preparedStatement.setObject(parameterIndex, element == null ? "null" : element.getTextTrim(), parameterType);

                dBUtil.prepareValue(preparedStatement, i, tableElement, column);
            }
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new LoadMainException(DB_UNKNOWN_SQL_ERROR, "生成sql语句或执行时出错", e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                throw new LoadMainException(GET_DB_CLOSE_CONNECTION_ERROR, "关闭数PreparedStatement连接时出错", e);
            }
        }
    }

    private void preparePptUpdateSql(Element tableElement, Table table) throws LoadMainException {
        String _and = " and ";
        StringBuilder sql = new StringBuilder();
        List<Column> columns = table.getColumnList();
        List<Column> keyList = table.getKeyColumnList();
        sql.append("update ");
//        sql.append(table.getSchema());
//        sql.append(".");
        sql.append(table.getTableName());
        sql.append(" set ");
        for (int i = 0; i < columns.size(); i++) {
            Column column = columns.get(i);
            sql.append(column.getColumnName());
            sql.append("=?,");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" where ");
        for (int i = 0; i < keyList.size(); i++) {
            Column column = keyList.get(i);
            sql.append(column.getColumnName());
            sql.append("=?");
            sql.append(_and);
        }
        sql.delete(sql.length() - _and.length(), sql.length());
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dBUtil.getCurrentConnection();
            preparedStatement = connection.prepareStatement(sql.toString());
            for (int i = 0; i < columns.size(); i++) {
                Column column = columns.get(i);
                dBUtil.prepareValue(preparedStatement, i, tableElement, column);
            }
            for (int i = 0; i < keyList.size(); i++) {
                Column column = keyList.get(i);
                dBUtil.prepareValue(preparedStatement, columns.size() + i, tableElement, column);
            }
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new LoadMainException(DB_UNKNOWN_SQL_ERROR, "生成sql语句或执行时出错", e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                throw new LoadMainException(GET_DB_CLOSE_CONNECTION_ERROR, "关闭数据库连接时出错", e);
            }
        }
    }

    private long preparePptSql(final Element tableElement, String tableName) throws LoadMainException {

        final Table table = dBUtil.getColumn(tableName);

        System.out.println(table);
        if (table == null) {
            throw new LoadMainException(GET_TABLE_COLUMN_ERROR, "Can not find table [" + tableName + "]");
        }
        if (table.getKeyColumnList() == null || !dBUtil.hasRow(tableElement, table)) {
            preparePptInsertSql(tableElement, table);
        } else {
            preparePptUpdateSql(tableElement, table);
        }
        return _NORMAL;
    }

    private void prepareHisInsertSql(Element tableElement, Table table) throws LoadMainException {
        StringBuilder sql = new StringBuilder();
        StringBuilder sb1 = new StringBuilder();
        StringBuilder sb2 = new StringBuilder();
        List<Column> columns = table.getColumnList();
        sql.append("insert into ");
//        sql.append(table.getSchema());
//        sql.append(".");
        sql.append(table.getTableName());
        for (int i = 0; i < columns.size(); i++) {
            Column column = columns.get(i);
            sb1.append(column.getColumnName()).append(",");
            sb2.append("?,");
        }
        sb1.deleteCharAt(sb1.length() - 1);
        sb2.deleteCharAt(sb2.length() - 1);
        sql.append(" (");
        sql.append(sb1);
        sql.append(") values (");
        sql.append(sb2);
        sql.append(")");
        // logger.info(sql.toString());
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dBUtil.getCurrentConnection();
            preparedStatement = connection.prepareStatement(sql.toString());
            for (int i = 0; i < columns.size(); i++) {
                Column column = columns.get(i);
                dBUtil.prepareValue(preparedStatement, i, tableElement, column);
            }
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new LoadMainException(DB_UNKNOWN_SQL_ERROR, "生成sql语句或执行时出错", e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                throw new LoadMainException(GET_DB_CLOSE_CONNECTION_ERROR, "关闭数据库连接时出错", e);
            }
        }
    }

    private long prepareHisSql(final Element tableElement, String tableName) throws LoadMainException {
        final Table table = dBUtil.getColumn(tableName);
        if (table == null) {
            throw new LoadMainException(GET_TABLE_COLUMN_ERROR, "根据表明无法找到列信息");
        }
        prepareHisInsertSql(tableElement, table);
        return _NORMAL;
    }

    public String getHisQueueName() {
        return hisQueueName;
    }

    public void setHisQueueName(String hisQueueName) {
        this.hisQueueName = hisQueueName;
    }

    public String getHisErrorQueueName() {
        return hisErrorQueueName;
    }

    public void setHisErrorQueueName(String hisErrorQueueName) {
        this.hisErrorQueueName = hisErrorQueueName;
    }

}
